﻿

CREATE proc [Projects].[SaveProject]
@ProjectID			int, 
@Intern				bit,
@ShortName			nvarchar(50), 
@PurchaserID		int, 
@InvoiceToID		int,
@FirstContactDate	datetime2(7), 
@FirstContactUserID	int, 
@Address			nvarchar(50), 
@EPLZ				int, 
@ImplementationDate	datetime2(7), 
@CustomerSource		int, 
@CustomerGroup		tinyint = 0, 
@CalculationUserID	int, 
@SalesUserID		int, 
@FileUserID			int, 
@UserID				int,
@ProjectStatus		tinyint,
@OrderDate			datetime2(7),
@FinishDate			datetime2(7),
@FilePath			nvarchar(255),
@Distance			decimal(11,2),

--Attributes
@GaToHm				tinyint, 
@GaToAl				tinyint, 
@GaToDi				tinyint, 
@EiToSi				tinyint, 
@EiToFl				tinyint, 
@AnToSeRo			tinyint, 
@AnToSi				tinyint, 
@AnToFl				tinyint, 
@AnToSr				tinyint, 
@SbGa				tinyint, 
@SbGh				tinyint, 
@SbHs				tinyint, 
@SbHsAverage		decimal(11,2),
@AaEi				tinyint, 
@AaZa				tinyint, 
@AaGe				tinyint,
@AaEf				tinyint,
@Carport			tinyint,
@LeDe				tinyint,
@LeMo				tinyint,
@LeUk				tinyint,
@LeBr				tinyint,
@LeBrAverage		decimal(11,2),
@LeRe				tinyint,
@LeReAverage		decimal(11,2),
@LeBl				tinyint,
@LeBlAverage		decimal(11,2),
--Values
@ErloesSuHw			decimal(11,2), 
@ErloesSuPr			decimal(11,2), 
@ErloesSuMo			decimal(11,2),
@ErloesRaPr			decimal(11,2), 
@ErloesRaLe			decimal(11,2), 
@ErloesRaPro		decimal(11,2), 
@ErloesSf			decimal(11,2), 
@ErloesEm			decimal(11,2), 
@ErloesFa			decimal(11,2),
@CostHandelsware	decimal(11,2), 
@CostProduktion		decimal(11,2), 
@CostMontage		decimal(11,2), 
@CostAdministration	decimal(11,2), 
@ExtraCostBIPercent	decimal(11,2), 
@ExtraCostBIFix		decimal(11,2), 
@ExtraCostPrEPercent	decimal(11,2), 
@ExtraCostPrEFix	decimal(11,2), 
@ExtraCostErfGar	decimal(11,2), 
@ExtraCostHaftRuecklage	decimal(11,2), 
@ExtraCostSkonto	decimal(11,2), 
@ExtraCostProvisionDB	decimal(11,2), 
@TimeProduktion		decimal(11,2), 
@TimeMontage		decimal(11,2), 
@TimeAdministration	decimal(11,2),
@TimeProducts		decimal(11,2)

as
begin
	set nocount on

	declare @rc int,
			@now datetime2(7) = sysdatetime(),
			@logText nvarchar(150)

	if @ShortName is null
	begin
		Select @ProjectID 'ProjectID', -1 'Return', 'ShortName is null' 'Error' 
		goto WithError
	end

	set xact_abort on

	begin tran
	
	begin try
		
		;with sel
		as
		(
			select	@ProjectID			'ProjectID',
					@Intern				'Intern',
					@ShortName			'ShortName', 
					@PurchaserID		'PurchaserID', 
					@InvoiceToID		'InvoiceToID',
					@FirstContactDate	'FirstContactDate', 
					@FirstContactUserID	'FirstContactUserID', 
					@Address			'Address', 
					@EPLZ				'EPLZ', 
					@ImplementationDate	'ImplementationDate', 
					@CustomerSource		'CustomerSource', 
					@CustomerGroup		'CustomerGroup', 
					@CalculationUserID	'CalculationUserID', 
					@SalesUserID		'SalesUserID', 
					@FileUserID			'FileUserID', 
					@UserID				'UserID',
					@ProjectStatus		'ProjectStatus',
					@OrderDate			'OrderDate',
					@FinishDate			'FinishDate',
					@FilePath			'FilePath',
					@Distance			'Distance',
					@now				'CreateOn',
					@UserID				'CreateBy',
					@now				'LastChangedOn',
					@UserID				'LastChangedBy'
		)
		merge into Projects.datProjects t
		using sel s on
			s.ProjectID = t.ProjectID
		when not matched by target then
		insert (
				[Intern],
				ShortName,
				PurchaserID, 
				InvoiceToID,
				FirstContactDate, 
				FirstContactUserID, 
				[Address], 
				EPLZ, 
				ImplementationDate, 
				CustomerSource, 
				CustomerGroup, 
				CalculationUserID, 
				SalesUserID, 
				FileUserID, 
				CreateOn, 
				CreateBy, 
				LastChangedOn, 
				LastChangedBy, 
				ProjectStatus,
				OrderDate,
				FinishDate,
				FilePath,
				Distance
				)
		values (
				s.[Intern],
				s.ShortName,
				s.PurchaserID, 
				s.InvoiceToID,
				s.FirstContactDate, 
				s.FirstContactUserID, 
				s.[Address], 
				s.EPLZ, 
				s.ImplementationDate, 
				s.CustomerSource, 
				s.CustomerGroup, 
				s.CalculationUserID, 
				s.SalesUserID, 
				s.FileUserID, 
				s.CreateOn, 
				s.CreateBy, 
				s.LastChangedOn, 
				s.LastChangedBy, 
				s.ProjectStatus,
				s.OrderDate,
				s.FinishDate,
				s.FilePath,
				s.Distance
				)
		when matched then
		update
			set
				[Intern]				= s.[Intern],
				ShortName				= s.ShortName,
				PurchaserID				= s.PurchaserID, 
				InvoiceToID				= s.InvoiceToID,
				FirstContactDate		= s.FirstContactDate, 
				FirstContactUserID		= s.FirstContactUserID, 
				[Address]				= s.[Address], 
				EPLZ					= s.EPLZ, 
				ImplementationDate		= s.ImplementationDate, 
				CustomerSource			= s.CustomerSource, 
				CustomerGroup			= s.CustomerGroup, 
				CalculationUserID		= s.CalculationUserID, 
				SalesUserID				= s.SalesUserID, 
				FileUserID				= s.FileUserID, 
				LastChangedOn			= s.LastChangedOn, 
				LastChangedBy			= s.LastChangedBy, 
				ProjectStatus			= s.ProjectStatus,
				OrderDate				= s.OrderDate,
				FinishDate				= s.FinishDate,
				FilePath				= s.FilePath,
				Distance				= s.Distance
				;

		if coalesce(@ProjectID,0) = 0
		begin
			set @ProjectID = @@IDENTITY
		end

		;with selDetails
		as
		(
			select	@ProjectID		'ProjectID',
					@GaToHm			'GaToHm', 
					@GaToAl			'GaToAl', 
					@GaToDi			'GaToDi', 
					@EiToSi			'EiToSi', 
					@EiToFl			'EiToFl', 
					@AnToSeRo		'AnToSeRo', 
					@AnToSi			'AnToSi', 
					@AnToFl			'AnToFl', 
					@AnToSr			'AnToSr', 
					@SbGa			'SbGa', 
					@SbGh			'SbGh', 
					@SbHs			'SbHs', 
					@AaEi			'AaEi', 
					@AaZa			'AaZa', 
					@AaGe			'AaGe', 
					@AaEf			'AaEf', 
					@Carport		'Carport', 
					@LeDe			'LeDe', 
					@LeMo			'LeMo', 
					@LeUk			'LeUk', 
					@LeBr			'LeBr', 
					@LeBrAverage	'LeBrAverage', 
					@LeRe			'LeRe', 
					@LeReAverage	'LeReAverage', 
					@LeBl			'LeBl', 
					@LeBlAverage	'LeBlAverage'
		)
		merge into [Projects].[datProjectsAttributes] t
		using selDetails s on
			s.ProjectID = t.ProjectID
		when not matched then
		insert (
				ProjectID, 
				GaToHm, 
				GaToAl, 
				GaToDi, 
				EiToSi, 
				EiToFl, 
				AnToSeRo, 
				AnToSi, 
				AnToFl, 
				AnToSr, 
				SbGa, 
				SbGh, 
				SbHs, 
				AaEi, 
				AaZa, 
				AaGe, 
				AaEf, 
				Carport, 
				LeDe, 
				LeMo, 
				LeUk, 
				LeBr, 
				LeBrAverage, 
				LeRe, 
				LeReAverage, 
				LeBl, 
				LeBlAverage
				)
		values (
				s.ProjectID, 
				s.GaToHm, 
				s.GaToAl, 
				s.GaToDi, 
				s.EiToSi, 
				s.EiToFl, 
				s.AnToSeRo, 
				s.AnToSi, 
				s.AnToFl, 
				s.AnToSr, 
				s.SbGa, 
				s.SbGh, 
				s.SbHs, 
				s.AaEi, 
				s.AaZa, 
				s.AaGe, 
				s.AaEf, 
				s.Carport, 
				s.LeDe, 
				s.LeMo, 
				s.LeUk, 
				s.LeBr, 
				s.LeBrAverage, 
				s.LeRe, 
				s.LeReAverage, 
				s.LeBl, 
				s.LeBlAverage
				)
		when matched then
		update
			set
				GaToHm		= s.GaToHm, 
				GaToAl		= s.GaToAl, 
				GaToDi		= s.GaToDi, 
				EiToSi		= s.EiToSi, 
				EiToFl		= s.EiToFl, 
				AnToSeRo	= s.AnToSeRo, 
				AnToSi		= s.AnToSi, 
				AnToFl		= s.AnToFl, 
				AnToSr		= s.AnToSr, 
				SbGa		= s.SbGa, 
				SbGh		= s.SbGh, 
				SbHs		= s.SbHs, 
				AaEi		= s.AaEi, 
				AaZa		= s.AaZa, 
				AaGe		= s.AaGe, 
				AaEf		= s.AaEf, 
				Carport		= s.Carport, 
				LeDe		= s.LeDe, 
				LeMo		= s.LeMo, 
				LeUk		= s.LeUk, 
				LeBr		= s.LeBr, 
				LeBrAverage	= s.LeBrAverage, 
				LeRe		= s.LeRe, 
				LeReAverage	= s.LeReAverage, 
				LeBl		= s.LeBl, 
				LeBlAverage	= s.LeBlAverage
				;


		;with valDetails
		as
		(
			select	@ProjectID				'ProjectID',
					@ErloesSuHw				'ErloesSuHw', 
					@ErloesSuPr				'ErloesSuPr', 
					@ErloesSuMo				'ErloesSuMo',
					@ErloesRaPr				'ErloesRaPr', 
					@ErloesRaLe				'ErloesRaLe', 
					@ErloesRaPro			'ErloesRaPro', 
					@ErloesSf				'ErloesSf', 
					@ErloesEm				'ErloesEm', 
					@ErloesFa				'ErloesFa',
					@CostHandelsware		'CostHandelsware', 
					@CostProduktion			'CostProduktion', 
					@CostMontage			'CostMontage', 
					@CostAdministration		'CostAdministration', 
					@ExtraCostBIPercent		'ExtraCostBIPercent', 
					@ExtraCostBIFix			'ExtraCostBIFix', 
					@ExtraCostPrEPercent	'ExtraCostPrEPercent', 
					@ExtraCostPrEFix		'ExtraCostPrEFix', 
					@ExtraCostErfGar		'ExtraCostErfGar', 
					@ExtraCostHaftRuecklage	'ExtraCostHaftRuecklage', 
					@ExtraCostSkonto		'ExtraCostSkonto', 
					@ExtraCostProvisionDB	'ExtraCostProvisionDB', 
					@TimeProduktion			'TimeProduktion', 
					@TimeMontage			'TimeMontage', 
					@TimeProducts			'TimeProducts',
					@TimeAdministration		'TimeAdministration'
		)
		merge into [Projects].[datProjectsValues] t
		using valDetails s on
			s.ProjectID = t.ProjectID
		when not matched by target then
		insert (
				ProjectID, 
				ErloesSuHw, 
				ErloesSuPr, 
				ErloesSuMo, 
				ErloesRaPr, 
				ErloesRaLe, 
				ErloesRaPro, 
				ErloesSf, 
				ErloesEm, 
				ErloesFa, 
				CostHandelsware, 
				CostProduktion, 
				CostMontage, 
				CostAdministration, 
				ExtraCostBIPercent, 
				ExtraCostBIFix, 
				ExtraCostPrEPercent, 
				ExtraCostPrEFix, 
				ExtraCostErfGar, 
				ExtraCostHaftRuecklage, 
				ExtraCostSkonto, 
				ExtraCostProvisionDB, 
				TimeProduktion, 
				TimeMontage, 
				TimeProducts,
				TimeAdministration
				)
		values (
				s.ProjectID, 
				s.ErloesSuHw, 
				s.ErloesSuPr, 
				s.ErloesSuMo, 
				s.ErloesRaPr, 
				s.ErloesRaLe, 
				s.ErloesRaPro, 
				s.ErloesSf, 
				s.ErloesEm, 
				s.ErloesFa, 
				s.CostHandelsware, 
				s.CostProduktion, 
				s.CostMontage, 
				s.CostAdministration, 
				s.ExtraCostBIPercent, 
				s.ExtraCostBIFix, 
				s.ExtraCostPrEPercent, 
				s.ExtraCostPrEFix, 
				s.ExtraCostErfGar, 
				s.ExtraCostHaftRuecklage, 
				s.ExtraCostSkonto, 
				s.ExtraCostProvisionDB, 
				s.TimeProduktion, 
				s.TimeMontage, 
				s.TimeProducts,
				s.TimeAdministration
				)
		when matched then
		update
			set
				ErloesSuHw				= s.ErloesSuHw, 
				ErloesSuPr				= s.ErloesSuPr, 
				ErloesSuMo				= s.ErloesSuMo, 
				ErloesRaPr				= s.ErloesRaPr, 
				ErloesRaLe				= s.ErloesRaLe, 
				ErloesRaPro				= s.ErloesRaPro, 
				ErloesSf				= s.ErloesSf, 
				ErloesEm				= s.ErloesEm, 
				ErloesFa				= s.ErloesFa, 
				CostHandelsware			= s.CostHandelsware, 
				CostProduktion			= s.CostProduktion, 
				CostMontage				= s.CostMontage, 
				CostAdministration		= s.CostAdministration, 
				ExtraCostBIPercent		= s.ExtraCostBIPercent, 
				ExtraCostBIFix			= s.ExtraCostBIFix, 
				ExtraCostPrEPercent		= s.ExtraCostPrEPercent, 
				ExtraCostPrEFix			= s.ExtraCostPrEFix, 
				ExtraCostErfGar			= s.ExtraCostErfGar, 
				ExtraCostHaftRuecklage	= s.ExtraCostHaftRuecklage, 
				ExtraCostSkonto			= s.ExtraCostSkonto, 
				ExtraCostProvisionDB	= s.ExtraCostProvisionDB, 
				TimeProduktion			= s.TimeProduktion, 
				TimeMontage				= s.TimeMontage, 
				TimeProducts			= s.TimeProducts,
				TimeAdministration		= s.TimeAdministration
				;


	end try
	begin catch
		Select @ProjectID 'ProjectID', -1 'Return', ERROR_MESSAGE() 'Error' 
		goto WithError
	end catch

	select @ProjectID 'ProjectID', 0 'Return', null 'Error'

	commit tran
	
	return 0

WithError:
	if @@TRANCOUNT > 0
	begin
		set @logText = 'Projects.SaveProject: ' + cast(@ProjectID as char(10))
		exec @rc = dbo.SaveLog
			@UserID = @UserID,
			@LogType = 'S',
			@LogArea = 'Pr',
			@LogDetails = @logText,
			@IsError = 1
		rollback tran
	end
	return -1
end
GO


